﻿using System;
using System.Data;
using Dng.DataFactory;
using Dng.Utilities;
using ShippingManage.DataAccessor.Entities;

namespace ShippingManage.DataAccessor.Models
{
	public class CustomerModel
	{
		#region Load information from database
		// Load object data from database
		public Customer Get(Guid id)
		{
			Customer result = null;
			ProviderFactory dataPF = new ProviderFactory();
			dataPF.OpenConnection();
			try
			{
				result = Get(dataPF, id);
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				dataPF.CloseConnection();
				dataPF.Dispose();
				dataPF = null;
			}
			return result;
		}

		// Load object data from database
		public Customer Get(ProviderFactory dataPF, Guid id)
		{
			var cmd = dataPF.CreateCommand("SELECT * FROM System.Data.DataRowView WHERE Id=@Id");
			cmd.Parameters.Add(dataPF.CreateParameter("@Id", DbType.StringFixedLength, Services.I.G2S(id)));
			return dataPF.FillSingle<Customer>(cmd.ExecuteReader());
		}

		#endregion

		#region Save information into database
		// Save object data into database
		public void Save(Customer obj)
		{
			ProviderFactory dataPF = new ProviderFactory();
			dataPF.OpenConnection();
			try
			{
				Save(dataPF, obj);
			}
			finally
			{
				dataPF.CloseConnection();
				dataPF.Dispose();
				dataPF = null;
			}
		}

		// Save object data into database
		public void Save(ProviderFactory dataPF, Customer obj)
		{

			// TO DO: Your validate code here!

			// TO DO: Check new!
			if (obj.Id == Guid.Empty)
			{
				obj.Id = Guid.NewGuid();
				Add(dataPF, obj);
			}
			else
				Update(dataPF, obj);
		}

		// Add new object data into database
		public void Add(ProviderFactory dataPF, Customer obj)
		{

			// TO DO: Your validate code before adding new here!

			string insertSQL = @"INSERT INTO System.Data.DataRowView (
														Id,
														Created,
														CreatedId,
														Modified,
														ModifiedId,
														FirstName,
														LastName,
														MiddleName,
														Address,
														City,
														State,
														ZipCode,
														Phone,
														Email,
														Ward,
														District,
														HomePhone,
														CellPhone,
														CustomerId
													) VALUES (
														@Id,
														@Created,
														@CreatedId,
														@Modified,
														@ModifiedId,
														@FirstName,
														@LastName,
														@MiddleName,
														@Address,
														@City,
														@State,
														@ZipCode,
														@Phone,
														@Email,
														@Ward,
														@District,
														@HomePhone,
														@CellPhone,
														@CustomerId
													)";
			IDbCommand command = dataPF.CreateCommand(insertSQL);

			command.Parameters.Add(dataPF.CreateParameter("@Id", DbType.StringFixedLength, Services.I.G2S(obj.Id)));
			command.Parameters.Add(dataPF.CreateParameter("@Created", DbType.StringFixedLength, Services.I.DT2S(obj.Created)));
			command.Parameters.Add(dataPF.CreateParameter("@CreatedId", DbType.StringFixedLength, Services.I.G2S(obj.CreatedId)));
			command.Parameters.Add(dataPF.CreateParameter("@Modified", DbType.StringFixedLength, Services.I.DT2S(obj.Modified)));
			command.Parameters.Add(dataPF.CreateParameter("@ModifiedId", DbType.StringFixedLength, Services.I.G2S(obj.ModifiedId)));
			command.Parameters.Add(dataPF.CreateParameter("@FirstName", DbType.String, obj.FirstName));
			command.Parameters.Add(dataPF.CreateParameter("@LastName", DbType.String, obj.LastName));
			command.Parameters.Add(dataPF.CreateParameter("@MiddleName", DbType.String, obj.MiddleName));
			command.Parameters.Add(dataPF.CreateParameter("@Address", DbType.String, obj.Address));
			command.Parameters.Add(dataPF.CreateParameter("@City", DbType.String, obj.City));
			command.Parameters.Add(dataPF.CreateParameter("@State", DbType.String, obj.State));
			command.Parameters.Add(dataPF.CreateParameter("@ZipCode", DbType.String, obj.ZipCode));
			command.Parameters.Add(dataPF.CreateParameter("@Phone", DbType.String, obj.Phone));
			command.Parameters.Add(dataPF.CreateParameter("@Email", DbType.String, obj.Email));
			command.Parameters.Add(dataPF.CreateParameter("@Ward", DbType.String, obj.Ward));
			command.Parameters.Add(dataPF.CreateParameter("@District", DbType.String, obj.District));
			command.Parameters.Add(dataPF.CreateParameter("@HomePhone", DbType.String, obj.HomePhone));
			command.Parameters.Add(dataPF.CreateParameter("@CellPhone", DbType.String, obj.CellPhone));
			command.Parameters.Add(dataPF.CreateParameter("@CustomerId", DbType.String, obj.CustomerId));

			command.ExecuteNonQuery();
		}

		// Update existing object data into database
		public void Update(ProviderFactory dataPF, Customer obj)
		{

			// TO DO: Your validate code before updating here!

			string updateSQL = @"UPDATE System.Data.DataRowView SET 
														Created=@Created,
														CreatedId=@CreatedId,
														Modified=@Modified,
														ModifiedId=@ModifiedId,
														FirstName=@FirstName,
														LastName=@LastName,
														MiddleName=@MiddleName,
														Address=@Address,
														City=@City,
														State=@State,
														ZipCode=@ZipCode,
														Phone=@Phone,
														Email=@Email,
														Ward=@Ward,
														District=@District,
														HomePhone=@HomePhone,
														CellPhone=@CellPhone,
														CustomerId=@CustomerId
													WHERE Id=@Id";
			IDbCommand command = dataPF.CreateCommand(updateSQL);

			command.Parameters.Add(dataPF.CreateParameter("@Id", DbType.StringFixedLength, Services.I.G2S(obj.Id)));
			command.Parameters.Add(dataPF.CreateParameter("@Created", DbType.StringFixedLength, Services.I.DT2S(obj.Created)));
			command.Parameters.Add(dataPF.CreateParameter("@CreatedId", DbType.StringFixedLength, Services.I.G2S(obj.CreatedId)));
			command.Parameters.Add(dataPF.CreateParameter("@Modified", DbType.StringFixedLength, Services.I.DT2S(obj.Modified)));
			command.Parameters.Add(dataPF.CreateParameter("@ModifiedId", DbType.StringFixedLength, Services.I.G2S(obj.ModifiedId)));
			command.Parameters.Add(dataPF.CreateParameter("@FirstName", DbType.String, obj.FirstName));
			command.Parameters.Add(dataPF.CreateParameter("@LastName", DbType.String, obj.LastName));
			command.Parameters.Add(dataPF.CreateParameter("@MiddleName", DbType.String, obj.MiddleName));
			command.Parameters.Add(dataPF.CreateParameter("@Address", DbType.String, obj.Address));
			command.Parameters.Add(dataPF.CreateParameter("@City", DbType.String, obj.City));
			command.Parameters.Add(dataPF.CreateParameter("@State", DbType.String, obj.State));
			command.Parameters.Add(dataPF.CreateParameter("@ZipCode", DbType.String, obj.ZipCode));
			command.Parameters.Add(dataPF.CreateParameter("@Phone", DbType.String, obj.Phone));
			command.Parameters.Add(dataPF.CreateParameter("@Email", DbType.String, obj.Email));
			command.Parameters.Add(dataPF.CreateParameter("@Ward", DbType.String, obj.Ward));
			command.Parameters.Add(dataPF.CreateParameter("@District", DbType.String, obj.District));
			command.Parameters.Add(dataPF.CreateParameter("@HomePhone", DbType.String, obj.HomePhone));
			command.Parameters.Add(dataPF.CreateParameter("@CellPhone", DbType.String, obj.CellPhone));
			command.Parameters.Add(dataPF.CreateParameter("@CustomerId", DbType.String, obj.CustomerId));

			command.ExecuteNonQuery();
		}

		#endregion

		#region Delete information from database
		// Delete object data from database
		public void Delete(Guid id)
		{
			ProviderFactory dataPF = new ProviderFactory();
			dataPF.OpenConnection();
			try
			{
				Delete(dataPF, id);
			}
			catch (Exception ex)
			{
				dataPF.RollbackTransaction();
				throw ex;
			}
			finally
			{
				dataPF.CloseConnection();
				dataPF.Dispose();
				dataPF = null;
			}
		}

		// Delete object data from database
		public void Delete(ProviderFactory dataPF, Guid id)
		{
			string deleteSQL = @"DELETE FROM System.Data.DataRowView
														WHERE Id=@Id";
			IDbCommand command = dataPF.CreateCommand(deleteSQL);
			command.Parameters.Add(dataPF.CreateParameter("@Id", DbType.StringFixedLength, id));
			command.ExecuteNonQuery();
		}

		#endregion
	}
}
